package database;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.Set;

import recipie.core.Ingredient;
import recipie.core.IngredientCollection;
import recipie.core.MesureUnit;
import recipie.core.Recipe;
import recipie.core.RecipeCollection;

public class RecipeManager 
{
  private static Connection  con;
  private static Statement stmt;
  private static ResultSet rs; 
  private static String query;
  
  public static boolean insertRecipe(Recipe recipe) 
  {
    if((con = DatabaseManager.getDatabaseDriverAndConnect()) == null)
	  return false;
	
    try
	{
	  insertIntoRecipeTable(recipe);
	}
	catch(SQLException e)
	{
	  return false;
	}
		   
	return true;
  }

  public static boolean DeleteRecipe(Recipe recipe)
  {
    if(recipe == null)
	  return true;
	  
    if((con = DatabaseManager.getDatabaseDriverAndConnect()) == null)
	  return false;
			  
	query = "delete from recipe where name = '" + recipe.getName() + "';";
		 
	try
	{
      stmt = DatabaseManager.createQuery(stmt, con);
	  DatabaseManager.executeUpdate(stmt, query);
	}
	catch(SQLException e)
	{
	  return false;
	}
	    
	return true;
  }
  
  public static RecipeCollection loadRecipes() 
  {
    if((con = DatabaseManager.getDatabaseDriverAndConnect()) == null)
	  return null;
	  
	RecipeCollection recipeCollection = new RecipeCollection();
	  
	try
	{
	  query = "select * from recipe;";
	  stmt = DatabaseManager.createQuery(stmt, con);
	  rs = DatabaseManager.executeQuery(rs, stmt, query);
	  boolean prototype = false;
	  while(rs.next())
	  {
		prototype = rs.getString("prototype").equals("true")?true:false;
		
	    recipeCollection.addRecipe(new Recipe(rs.getString("name"), Integer.parseInt(rs.getString("difficulty")), prototype,
	    		                   Integer.parseInt(rs.getString("serving")), rs.getString("source"), null, null, null, null));
	  }
	  
	  recipeCollection = manageSubrecipes(recipeCollection);
	  recipeCollection = manageIngredientsAndQuantities(recipeCollection);
	}
    catch(SQLException e)
	{ 
	  return null;
	}
	
	return recipeCollection;
  }
  
  private static RecipeCollection manageSubrecipes(RecipeCollection recipeCollection)
  {
    Set<String> keys = recipeCollection.getRecipes().keySet();
	for(String k : keys)
	{
	  LinkedList<String> subRecipes = new LinkedList<String>();
	  String name = recipeCollection.getRecipes().get(k).getName();
	  query = "select * from recipe_subrecipe where recipe_name ='" + name + "';";
	  try
	  {
	    stmt = DatabaseManager.createQuery(stmt, con);
		rs = DatabaseManager.executeQuery(rs, stmt, query);
		while(rs.next())
		{
		  subRecipes.add(rs.getString("sub_recipe_name"));	
		}
		recipeCollection.getRecipes().get(k).setSubRecipes(subRecipes);
	  }
	  catch(SQLException e)
	  { 
	    return null;
	  }
    }  
	
	return recipeCollection;
  }
  
  private static RecipeCollection manageIngredientsAndQuantities(RecipeCollection recipeCollection)
  {
    Set<String> keys = recipeCollection.getRecipes().keySet();
	for(String k : keys)
    {
	  LinkedList<String> ingredients = new LinkedList<String>();
	  HashMap<String,Integer> quantities = new HashMap<String,Integer>(); 
	  String name = recipeCollection.getRecipes().get(k).getName();
      query = "select * from recipe_ingredient where recipe_name ='" + name + "';";
	  try
	  {
	    stmt = DatabaseManager.createQuery(stmt, con);
		rs = DatabaseManager.executeQuery(rs, stmt, query);
		while(rs.next())
		{
		  ingredients.add(rs.getString("ingredient_name"));
		  quantities.put(rs.getString("ingredient_name"), Integer.parseInt(rs.getString("quantity")));

			System.out.println(rs.getString("ingredient_name") + " " + Integer.parseInt(rs.getString("quantity")));
		}
		recipeCollection.getRecipes().get(k).setIngredients(ingredients);
		recipeCollection.getRecipes().get(k).setIngredientsQuantities(quantities);
	  }
	  catch(SQLException e)
	  { 
	    return null;
	  }
    }
	return  recipeCollection;
  }
  
  private static void insertIntoRecipeTable(Recipe recipe) throws SQLException
  {
	String name = recipe.getName();
    int difficulty = recipe.getDifficulty();
	String prototype = recipe.getPrototype()?"true":"false";
    int serving = recipe.getServing();
	String source = recipe.getSource();
	
	query = "insert into recipe values ('" + name + "'," + "'" + prototype + "'," +
	  	    "'" + source + "'," + "'" + difficulty + "'," + "'" + serving + "');";
	stmt = DatabaseManager.createQuery(stmt, con);
	DatabaseManager.executeUpdate(stmt, query);
	
	LinkedList<String> subRecipes = recipe.getSubRecipes();
	if(subRecipes != null)
	{
	  while(!subRecipes.isEmpty())
	  {
	    String subName = subRecipes.pop();
	    query = "insert into recipe_subrecipe values ('" + name + "'," + "'" + subName + "');";
	    stmt = DatabaseManager.createQuery(stmt, con);
	    DatabaseManager.executeUpdate(stmt, query);
	  }
	}
	
	if(recipe.getIngredients() == null)
	  return;
	
	LinkedList<String> ingredients = recipe.getIngredients();
	for(String ing : ingredients)
	{
      int quantity = recipe.getIngredientsQuantities() == null ? 1 : recipe.getIngredientsQuantities().get(ing);
	  query = "insert into recipe_ingredient values ('" + recipe.getName() + "'," + "'" + ing + 
			  "'," + "'" + quantity + "');";
	  stmt = DatabaseManager.createQuery(stmt, con);
	  DatabaseManager.executeUpdate(stmt, query);
	}
	
  }
  
}
